//
//  SQLiteDataBaseManager.m
//  18-SQLite
//
//  Created by 王道林 on 16/8/8.
//  Copyright © 2016年 王道林. All rights reserved.
//

#import "SQLiteDataBaseManager.h"
#import "Student.h"
@implementation SQLiteDataBaseManager


//  单例的创建方式, 只有一个对象
//内存管理时... 单例不能被释放...不要release一个单例
+ (SQLiteDataBaseManager *)shareManager
{
    static SQLiteDataBaseManager *manager = nil;
    //  保证线程安全的情况, 只执行一次
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[SQLiteDataBaseManager alloc] init];
    });
    return manager;
}

- (BOOL)openSQlite3
{
   NSArray *arr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *document = [arr firstObject];
    // 数据库SQLite3 文件类型,  .db
   NSString *dbPath = [document stringByAppendingPathComponent:@"student.db"];
    NSLog(@"%@", dbPath);
    
    // UTF8tring  属性, 将OC字符串转化c语言字符串
    // 参数1: 数据库文件路径
    // 参数2: 指向指针的指针, 该指针可以被修改. 数据库管理指针
  int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    BOOL isOpen = YES;
    // sqlite3_open 当路径文件不存在时, 文件会被SQLite创建并打开
    // 当路径文件存在时. 文件被直接打开
    // slite3_open 返回值 为int型, 0  SQLITE_OK 代表成功.  1代表失败;
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
        isOpen = YES;
    } else
    {
        NSLog(@"数据库打开失败");
        isOpen = NO;
    }
    
    
    return YES;
}

- (BOOL)closeSQLite3
{
    // 关闭数据时,  如果数据库处于打开状态, 则正常关闭, 显示成功
    
   int result = sqlite3_close(dbPoint);
    
    return [self isOperationREsult:result alertText:@"关闭数据库"];
}

- (BOOL)isOperationREsult:(int)result alertText:(NSString *)text
{
    BOOL isOperation = YES;
    if (result == SQLITE_OK) {
        NSLog(@"%@成功", text);
        isOperation = YES;
    } else
    {
        NSLog(@"%@失败", text);
        NSLog(@"%d", result);
        isOperation = NO;
    }
    return isOperation;

}


- (BOOL)crateTable
{
    // SQL语句
    // 创建表
    // create table 表名  (字段1, 类型 约束, 字段2, 字段3,....)
//    NSString *createSQL = @"create Table stu(stu_id integer primary key, stu_name text)";
    
    // primary key 一个表, 有且只能有一个主键
    // autoincrement 自增, 添加完该字段, 系统会自己创建一个数据库sqlite_sequence, 用来保存自增的值
    // not null  不能为空, 为空就会SQL报错
    
    // 删除表
    // SQL 语句::  drop table 表名
    
   NSString *createSQL = @"create Table stu2(stu_id integer primary key autoincrement, stu_name text not null)";
    
   int result = sqlite3_exec(dbPoint, [createSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationREsult:result alertText:@"创建表"];
    
    
    return YES;
}


- (BOOL)insertStu:(Student *)stu
{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into stu2 (stu_id, stu_name) values (null, '%@')", stu.stu_name];
 //   NSString *insertSQL = @"insert into stu1 values (110, '王黄')";
    
    int result = sqlite3_exec(dbPoint, [insertSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationREsult:result alertText:@"添加数据"];
    
    
    return YES;
    
    // 自增字段, autoincremet
    //  默认如果填入null, 值会从1开始
    //  如果填入其他值, 会从其他值开始, 例如: 填入110, 值从110开始计算.并进行存储
    
}




- (BOOL)deleteStu:(Student *)stu
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from stu2 where stu_id=%ld", stu.stu_id];
  int resaut = sqlite3_exec(dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
    
    return [self isOperationREsult:resaut alertText:@"删除数据"];
}


- (BOOL)deleteAll
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from stu2 where 1=1"];
    int resaut = sqlite3_exec(dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
    
    return [self isOperationREsult:resaut alertText:@"删除全部数据"];
}


- (BOOL)updataOldName:(NSString *)oldName newName:(NSString *)newName
{
    //  updata 表名 set 字段名 = 新值 where 字段名 = 旧值
    NSString *updataSQL = [NSString stringWithFormat:@"update stu2 set stu_name = '%@' where stu_name = '%@'", newName, oldName];
   int result = sqlite3_exec(dbPoint, [updataSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationREsult:result alertText:@"更新数据"];
    
}

// 查询

- (NSArray *)selectAll
{
    // select 字段名, 字段名, ....   from 表名  where  条件
    NSString *selectAll = @"select * from stu1";
    sqlite3_stmt *stmt = NULL;
    // 参数1: 数据库指针 sqlite3*
    // 参数2: SQL语句
    // 参数3: SQL语句的长度, -1
    // 参数4: 替身,查询出内容放入替身, 并将替身返回
  int result = sqlite3_prepare(dbPoint, [selectAll UTF8String], -1, &stmt, NULL);
    NSMutableArray *stuArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        // 遍历替身内容,
        // step 查询替身中有没有其他数据.
        // 当包含其他数据时, 结果为SQLITE_ROW..
        // 当不包含其他数据时, 结果为SQLITE_DONE
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 循环体中,去对应的每一列数据
            // 取出第0列的数据, 根据类型选择函数, int
            int stu_id = sqlite3_column_int(stmt, 0);
            // 取出第1 列的数据, 根据类型选择函数, text
            const unsigned char *stu_name = sqlite3_column_text(stmt, 1);
            Student *stu = [[Student alloc] init];
            stu.stu_id = stu_id;
            stu.stu_name = [NSString stringWithUTF8String:(const char *)stu_name];
            [stuArray addObject:stu];
        }
    }
    // 循环结束后, 销毁替身
    sqlite3_finalize(stmt);
    return stuArray;
}




@end
